package com.union.core.organization.dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.hnjowe.webdb.DBOperation;
import com.union.core.organization.dao.OrganizationManagerDao;
import com.union.core.organization.model.OrganizationManager;

public class OrganizationManagerDaoImpl implements OrganizationManagerDao{
	private DBOperation opera;
	private OrganizationManager organization;
	private static Logger log = Logger.getLogger(OrganizationManagerDaoImpl.class.getName());
	
	public OrganizationManagerDaoImpl(){
		
	}
	
	/**
	 * 查询机构信息列表
	 */
	public List<OrganizationManager> query(int pid,int start,int pageSize){
		
		String sqlstr1 = ""; 	//条件1
		 sqlstr1 = " and pid="+pid;
		
		List<OrganizationManager> OrganizationList = new ArrayList<OrganizationManager>();
		
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "select "
					+ "organization_id,organization_name,pid,organization_parent,organization_description,add_name," 
					+ " add_time,updae_name,update_time"
					+ " from organization " 
					+ " where organization_id>0" + sqlstr1
			        + " LIMIT ?,?";
			opera.prepStatment(sql);
			 opera.setInt(1, start);
			 opera.setInt(2, pageSize);
			ResultSet res = opera.executeQuery();
			int i = 0;
			while (null!=res && res.next()) {
				organization = new OrganizationManager();
				organization.setOrganization_id(res.getInt(1));
				organization.setOrganization_name(res.getString(2));
				organization.setPid(res.getInt(3));
				organization.setOrganization_parent(res.getString(4));
				organization.setOrganization_description(res.getString(5));
				organization.setAdd_name(res.getString(6));
				organization.setAdd_time(res.getString(7));
				organization.setUpdae_name(res.getString(8));
				organization.setUpdate_time(res.getString(9));
				OrganizationList.add(i, organization);
				i++;
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}	
	    return OrganizationList;	
	}	
	
	public List<OrganizationManager> queryList(int pid){
		List<OrganizationManager> OrganizationList = new ArrayList<OrganizationManager>();
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "select "
				+ "organization_id,organization_name,pid,organization_parent,organization_description,add_name," 
				+ "add_time,updae_name,update_time"
				+ " from organization " 
				+ " where pid =?" 
				+ " order by organization_id";
			opera.prepStatment(sql);
			opera.setInt(1, pid);
			ResultSet res = opera.executeQuery();
			int i = 0;
			while (res.next()) {
				organization = new OrganizationManager();
				organization.setOrganization_id(res.getInt(1));
				organization.setOrganization_name(res.getString(2));
				organization.setPid(res.getInt(3));
				organization.setOrganization_parent(res.getString(4));
				organization.setOrganization_description(res.getString(5));
				organization.setAdd_name(res.getString(6));
				organization.setAdd_time(res.getString(7));
				organization.setUpdae_name(res.getString(8));
				organization.setUpdate_time(res.getString(9));
				OrganizationList.add(i, organization);
				i++;
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return OrganizationList;
	}
	public List<OrganizationManager> queryList1(int organization_id){
		List<OrganizationManager> OrganizationList = new ArrayList<OrganizationManager>();
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "select "
				+ "organization_id,organization_name,pid,organization_parent,organization_description,add_name," 
				+ "add_time,updae_name,update_time"
				+ " from organization " 
				+ " where organization_id =?" 
				+ " order by organization_id";
			opera.prepStatment(sql);
			opera.setInt(1, organization_id);
			ResultSet res = opera.executeQuery();
			int i = 0;
			while (res.next()) {
				organization = new OrganizationManager();
				organization.setOrganization_id(res.getInt(1));
				organization.setOrganization_name(res.getString(2));
				organization.setPid(res.getInt(3));
				organization.setOrganization_parent(res.getString(4));
				organization.setOrganization_description(res.getString(5));
				organization.setAdd_name(res.getString(6));
				organization.setAdd_time(res.getString(7));
				organization.setUpdae_name(res.getString(8));
				organization.setUpdate_time(res.getString(9));
				OrganizationList.add(i, organization);
				i++;
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return OrganizationList;
	}
	public List<OrganizationManager> queryList(){
		List<OrganizationManager> OrganizationList = new ArrayList<OrganizationManager>();
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "select "
				+ "organization_id,organization_name,pid,organization_parent,organization_description,add_name," 
				+ "add_time,updae_name,update_time"
				+ " from organization " 
				+ " order by organization_id";
			opera.prepStatment(sql);
			ResultSet res = opera.executeQuery();
			int i = 0;
			while (res.next()) {
				organization = new OrganizationManager();
				organization.setOrganization_id(res.getInt(1));
				organization.setOrganization_name(res.getString(2));
				organization.setPid(res.getInt(3));
				organization.setOrganization_parent(res.getString(4));
				organization.setOrganization_description(res.getString(5));
				organization.setAdd_name(res.getString(6));
				organization.setAdd_time(res.getString(7));
				organization.setUpdae_name(res.getString(8));
				organization.setUpdate_time(res.getString(9));
				OrganizationList.add(i, organization);
				i++;
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return OrganizationList;
	}
	public List<OrganizationManager> queryList(String organization_parent){
		List<OrganizationManager> OrganizationList = new ArrayList<OrganizationManager>();
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "select "
				+ "organization_id,organization_name,pid,organization_parent,organization_description,add_name," 
				+ " add_time,updae_name,update_time"
				+ " from organization " 
				+ " where organization_parent =?" 
				+ "order by organization_id";
			opera.prepStatment(sql);
			opera.setString(1, organization_parent);
			ResultSet res = opera.executeQuery();
			int i = 0;
			while (res.next()) {
				organization = new OrganizationManager();
				organization.setOrganization_id(res.getInt(1));
				organization.setOrganization_name(res.getString(2));
				organization.setPid(res.getInt(3));
				organization.setOrganization_parent(res.getString(4));
				organization.setOrganization_description(res.getString(5));
				organization.setAdd_name(res.getString(6));
				organization.setAdd_time(res.getString(7));
				organization.setUpdae_name(res.getString(8));
				organization.setUpdate_time(res.getString(9));
				OrganizationList.add(i, organization);
				i++;
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return OrganizationList;
	}
	
	/**
	 * 查询机构数目
	 */
	public int ListCount(int pid){
		String sqlstr1 = ""; 	//条件1
		 sqlstr1 = " and pid="+pid;
		
		int totalSize = 0;

		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql= "select count(*) as totalSize "
						+ "from organization "
						+ "where organization_id>0"+ sqlstr1;
			opera.prepStatment(sql);
			ResultSet res = opera.executeQuery();
			if (res.next()) {
				totalSize = res.getInt(1);
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return totalSize;
	}
	
	/**
	 * 查询单个机构
	 * @param pid
	 * @param organization_name
	 * @return
	 */
	public OrganizationManager query(int pid,String organization_name){
		OrganizationManager organization = null;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			
			String sql = "select" 
				   + " organization_id,organization_name,pid,organization_parent,organization_description,add_name," 
				   + " add_time,updae_name,update_time" 
				   + " from organization" 
				   + " where pid=? and organization_name=?";
			opera.prepStatment(sql);
			opera.setInt(1, pid);
			opera.setString(2, organization_name);
			ResultSet res = opera.executeQuery();
			while(null!=res && res.next()){
				organization = new OrganizationManager();
				organization.setOrganization_id(res.getInt(1));
				organization.setOrganization_name(res.getString(2));
				organization.setPid(res.getInt(3));
				organization.setOrganization_parent(res.getString(4));
				organization.setOrganization_description(res.getString(5));
				organization.setAdd_name(res.getString(6));
				organization.setAdd_time(res.getString(7));
				organization.setUpdae_name(res.getString(8));
				organization.setUpdate_time(res.getString(9));
			}
		}catch(Exception e){
			log.error(e);
		}finally{
			opera.closePres();
			opera.closeConn();
		}
		return organization;
	}
	
	
	
	/**
	 * 查询单个机构
	 * @param organization_id
	 * @return
	 */
	public OrganizationManager query(int organization_id){
		OrganizationManager organization = null;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			
			String sql = "select" 
				   + " organization_id,organization_name,pid,organization_parent,organization_description,add_name," 
				   + " add_time,updae_name,update_time" 
				   + " from organization" 
				   + " where organization_id=?";
			opera.prepStatment(sql);
			opera.setInt(1, organization_id);
			ResultSet res = opera.executeQuery();
			while(null!=res && res.next()){
				organization = new OrganizationManager();
				organization.setOrganization_id(res.getInt(1));
				organization.setOrganization_name(res.getString(2));
				organization.setPid(res.getInt(3));
				organization.setOrganization_parent(res.getString(4));
				organization.setOrganization_description(res.getString(5));
				organization.setAdd_name(res.getString(6));
				organization.setAdd_time(res.getString(7));
				organization.setUpdae_name(res.getString(8));
				organization.setUpdate_time(res.getString(9));
			}
		}catch(Exception e){
			log.error(e);
		}finally{
			opera.closePres();
			opera.closeConn();
		}
		return organization;
	}
	
	public OrganizationManager query(String organization_name){
		OrganizationManager organization = null;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			
			String sql = "select" 
				   + " organization_id,organization_name,pid,organization_parent,organization_description,add_name," 
				   + " add_time,updae_name,update_time" 
				   + " from organization" 
				   + " where organization_name=?";
			opera.prepStatment(sql);
			opera.setString(1, organization_name);
			ResultSet res = opera.executeQuery();
			while(null!=res && res.next()){
				organization = new OrganizationManager();
				organization.setOrganization_id(res.getInt(1));
				organization.setOrganization_name(res.getString(2));
				organization.setPid(res.getInt(3));
				organization.setOrganization_parent(res.getString(4));
				organization.setOrganization_description(res.getString(5));
				organization.setAdd_name(res.getString(6));
				organization.setAdd_time(res.getString(7));
				organization.setUpdae_name(res.getString(8));
				organization.setUpdate_time(res.getString(9));
			}
		}catch(Exception e){
			log.error(e);
		}finally{
			opera.closePres();
			opera.closeConn();
		}
		return organization;
	}
	
	public OrganizationManager query1(int manager_id){
		OrganizationManager organization = null;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			
			String sql = "select" 
				   + " a.manager_id,b.organization_id,b.organization_name,b.pid,b.organization_parent" 
				   + " from sys_manager a,personnel b" 
				   + " where a.manager_id=? and a.personnel_id=b.personnel_id";
			opera.prepStatment(sql);
			opera.setInt(1, manager_id);
			ResultSet res = opera.executeQuery();
			while(null!=res && res.next()){
				organization = new OrganizationManager();
				organization.setManager_id(res.getInt(1));
				organization.setOrganization_id(res.getInt(2));
				organization.setOrganization_name(res.getString(3));
				organization.setPid(res.getInt(4));
				organization.setOrganization_parent(res.getString(5));
			}
		}catch(Exception e){
			log.error(e);
		}finally{
			opera.closePres();
			opera.closeConn();
		}
		return organization;
	}
	
	/**
	 * 
	 * @param organization
	 * @return
	 * 添加机构
	 */
	public boolean add(OrganizationManager organization){
    	boolean result = false;
    	try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "insert into organization(organization_name,pid,organization_parent," 
				    + "organization_description,add_name,add_time) " 
				    + "values (?,?,?,?,?,SYSDATE()) ";
			opera.prepStatment(sql);
			opera.setString(1, organization.getOrganization_name());
			opera.setInt(2, organization.getPid());
			opera.setString(3, organization.getOrganization_parent());
			opera.setString(4, organization.getOrganization_description());
			opera.setString(5, organization.getAdd_name());
			opera.execute();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
    }
	
	/**
	 * 修改机构信息
	 * @param organization
	 * @return
	 */
	public boolean edit(OrganizationManager organization){
		boolean result = false;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "update organization "							
					+ "set organization_name=?,pid=?,organization_parent=?,organization_description=?,"
					+ "updae_name=?,update_time=SYSDATE() "
					+ "where organization_id=?";
			opera.prepStatment(sql);
			opera.setString(1, organization.getOrganization_name());
			opera.setInt(2, organization.getPid());
			opera.setString(3, organization.getOrganization_parent());
			opera.setString(4, organization.getOrganization_description());
			opera.setString(5, organization.getUpdae_name());
			opera.setInt(6, organization.getOrganization_id());

			opera.executeUpdate();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}
	
	/**
	 * 删除机构
	 * @param organization_id
	 * @return
	 */
	public Boolean delete(int organization_id){
    	boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "delete from organization "
					+ "where organization_id=? ";
			opera.prepStatment(sql);
			opera.setInt(1, organization_id);
			opera.execute();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
    	
    }
	
	public boolean checkPid(int pid){
		boolean result = false;
	    try{
	      try{
	        opera = new DBOperation();
	        opera.transcationBegin();
	        String sql = "select * "
	                   + "from organization "
	                   + "where pid=?";
	        opera.prepStatment(sql);
	        opera.setInt(1,pid);
	        ResultSet res = opera.executeQuery();
	        if(res.next()){
	          result = true;
	        }
	      }catch(Exception ex){
	        result = false;
	      }
	    }catch(Exception e){
	      e.printStackTrace();
	    }finally{
	      opera.closePres();
	      opera.closeConn();
	    }
	    return result;
	}
	
	public boolean checkOrganizationName(String organization_name){
		boolean result = false;
	    try{
	      try{
	        opera = new DBOperation();
	        opera.transcationBegin();
	        String sql = "select * "
	                   + "from organization "
	                   + "where organization_name=?";
	        opera.prepStatment(sql);
	        opera.setString(1,organization_name);
	        ResultSet res = opera.executeQuery();
	        if(res.next()){
	          result = true;
	        }
	      }catch(Exception ex){
	        result = false;
	      }
	    }catch(Exception e){
	      e.printStackTrace();
	    }finally{
	      opera.closePres();
	      opera.closeConn();
	    }
	    return result;
	}
	
}